package com.erpoa.mapper;

import com.erpoa.entity.RecMatEntity;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;

/**
 * @author chenjimin
 * @Title: GoodsOutMaterialMapper
 * @ProjectName oaerp
 * @Description: TODO
 * @date 2020/9/2115:25
 */
@Mapper
public interface GoodsOutMaterialMapper {
    @Select("  select * " +
            "  from (SELECT DISTINCT A.FID fRecID," +
            "                        A.FORDERNO fOrderNo," +
            "                        A.FMATCODE fMatCode," +
            "                        A.FMATNAME fMatName," +
            "                        A.FOPTYPE fOpType," +
            "                        nvl(A.FRQSQTY,0) fRqsQty," +
            "                        nvl(A.FRSPQTY,0) fRspQty," +
            "                        A.FVENDERNAME fVenderCode," +
            "                        nvl( A.FPRODUCTYPE,' ') fType," +
            "                        A.FMACHINECODE fMachineCode," +
            "                        A.FRQSTIME," +
            "                        (SELECT NVL(SUM(CASE" +
            "                                          WHEN FOPTYPE = '领料' THEN" +
            "                                           FRSPQTY" +
            "                                          WHEN FOPTYPE = '退料' THEN" +
            "                                           FRSPQTY * -1" +
            "                                        END)," +
            "                                    0) AS fPlanOutQty" +
            "                           FROM MG_RECMAT R" +
            "                          WHERE R.FPLANID = A.FPLANID" +
            "                            AND R.FMATCODE = A.FMATCODE" +
            "                          GROUP BY FPLANID, FMATCODE) as fPlanOutQty, " +//--OVEROUTMAT
            "                        (SELECT DECODE(SFA12," +
            "                                       'KG'," +
            "                                       ROUND(SFA161 * FPLANQTY, 3)," +
            "                                       CEIL(ROUND(SFA161 * FPLANQTY, 3))) AS FPLANOUTQTY" +
            "                           FROM DS2.SFA_FILE" +
            "                          WHERE SFA01 = A.FORDERNO" +
            "                            AND SFA03 = A.FMATCODE) fPlanNeedQty, " + //--FPLANOUTQTY
            "                        FPROCSS fRemark" +
            "          FROM MG_RECMAT A, MG_ORDERPLAN B, DS2.TC_PGEN_FILE C" +
            "         WHERE A.FPLANID = B.FID" +
            "           AND B.FORDERSTATUS = '生产中'" +
            "           AND A.FOPTYPE = '领料'" +
            "           AND A.FFACTORYCODE = C.TC_PGEN02" +
            "           AND A.FWORKSHOPCODE = C.TC_PGEN03" +
            "            and FPRODUCTYPE  in ('硅油','油墨','笔头')" +  //TODO  CJM PROBLM ?????
            "           AND C.TC_PGEN01 = #{hrno}" +
            "           AND NVL(A.FRQSQTY, 0) <> NVL(FRSPQTY, 0))" +
            " where ((fPlanNeedQty - fPlanOutQty) > 0.001 or" +
            "       (fPlanNeedQty - fPlanOutQty) < 0)" +
            " ORDER BY FRQSTIME ")
    List<RecMatEntity> selectRecMatExistFlagByhrno(@Param("hrno") String hrno);

    @Select("select distinct  FWARECODE fWareCode,FSEATCODE fSeatCode,nvl(img10,'0.00')" +
            " erpStock from ds2.img_file A,dsx5.mg_recmat B where A.IMG01 = B.FMATCODE "
            + "and A.IMG02 = B.Fwarecode and A.IMG03 = B.Fseatcode and B.fid = #{fid}  and IMG04=' '")
    List<RecMatEntity> selectWareByFid(@Param("fid") String fid);

    @Select("select distinct  FWARECODE FWARECODE,FSEATCODE FSEATCODE,nvl(img10,'0.00') IMG10 from ds2.img_file A,dsx5.mg_recmat" +
            " B where A.IMG01 = B.FMATCODE "
            + " and A.IMG02 = B.Fwarecode and A.IMG03 = B.Fseatcode and B.fid = #{fRecID}  and IMG04=' '")
    HashMap selectWareSEatByFid(@Param("fRecID") String fRecID);

    @Select(" select nvl(sum(TC_X509),0) from dsx5.tc_x5_file where TC_X504 = #{fMatCode} and tc_x505 = '1' and tc_x506 = #{fWareCode} and tc_x507 = #{fSeatCode} ")
    Double selectSumxQtyByparam(@Param("fMatCode") String fMatCode, @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode);

    @Select("SELECT COUNT(TC_BAIMAM01) FROM DS2.TC_BAIMAM_FILE WHERE TC_BAIMAM01=#{fVenderCode} AND  TC_BAIMAM02=#{fModelNo} AND TC_BAIMAM07='A' AND TC_BAIMAM03=#{fMatCode} ")
    int countBaimamByParam(@Param("fVenderCode") String fVenderCode, @Param("fModelNo") String fModelNo, @Param("fMatCode") String fMatCode);

    @Select("SELECT COUNT(TC_BAIMAM01) FROM DS2.TC_BAIMAM_FILE WHERE TC_BAIMAM01=#{fVenderCode} AND  TC_BAIMAM02=#{fModelNo} AND TC_BAIMAM07='A' AND TC_BAIMAM03=#{fMatCode} "
            + " AND TC_BAIMAM06=#{fColor} ")
    int countBaimamByParamcolor(@Param("fVenderCode") String fVenderCode, @Param("fModelNo") String fModelNo, @Param("fMatCode") String fMatCode, @Param("fColor") String fColor);

    @Select("SELECT FFACTORYCODE,FWORKSHOPCODE FROM MG_RECMAT WHERE FID=#{fRecID}")
    HashMap selectffwcodeByfrecid(@Param("fRecID") String fRecID);

    @Select("SELECT FBARCODE,FQTY,FID FROM MG_BATCHMAT WHERE FRECEIVEDATE = (SELECT MIN(FRECEIVEDATE) FROM MG_BATCHMAT WHERE"
            + " FMATCODE =#{fMatCode} AND FVENDERCODE =#{fVenderCode} AND　FQTY>0 AND FFACTORYCODE=#{fFactoryCode} AND FWORKSHOPCODE=#{fWorkShopCode} ) " +
            "AND FQTY>0 AND FBARCODE=#{fBarCode}  AND FFACTORYCODE=#{fFactoryCode} AND FWORKSHOPCODE=#{fWorkShopCode} ")
    HashMap selectFBQDByParam(@Param("fMatCode") String fMatCode, @Param("fVenderCode") String fVenderCode, @Param("fFactoryCode") String fFactoryCode,
                              @Param("fWorkShopCode") String fWorkShopCode, @Param("fBarCode") String fBarCode);

    @Select(" SELECT NVL(FPRODUCTIONDATE -(SELECT MIN(FPRODUCTIONDATE) count FROM MG_BATCHMAT WHERE FMATCODE =#{fMatCode} AND FVENDERCODE = #{fVenderCode}" +
            " AND　FQTY>0 AND FFACTORYCODE=#{fFactoryCode} AND FWORKSHOPCODE=#{fWorkShopCode} AND"
            + " FRECEIVEDATE = (SELECT FRECEIVEDATE FROM MG_BATCHMAT WHERE FID =#{matID})), 0) FROM MG_BATCHMAT  WHERE FID =#{matID} ")
    int selectFprdatediffdateByParam(@Param("fMatCode") String fMatCode, @Param("fVenderCode") String fVenderCode,
                                     @Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode, @Param("matID") String matID);

    @Select(" SELECT COUNT(FBARCODE) FROM MG_BATCHMAT WHERE FBARCODE=#{fBarCode} AND FFACTORYCODE=#{fFactoryCode} AND FWORKSHOPCODE=#{fWorkShopCode} ")
    double selectbarcodeLasterByParam(@Param("fBarCode") String fBarCode, @Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Select(" SELECT NVL(FRQSQTY,0)-NVL(FRSPQTY,0) DIFFQTY,FRQSQTY,FRSPQTY FROM MG_RECMAT WHERE FID=#{fRecID} ")
    HashMap selectfqpQtyByrecId(@Param("fRecID") String fRecID);

    @Update("UPDATE MG_RECMAT SET FRSPQTY=NVL(FRSPQTY,0)+#{adjustQty},FRSPPER=#{sCode},FRSPTIME=SYSDATE,FVENDERNAME=#{fVenderCode} WHERE FID=#{fRecID}")
    int updateTranferMatByParam(@Param("adjustQty") double adjustQty, @Param("sCode") String sCode, @Param("fVenderCode") String fVenderCode, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMATDETAIL (FID ,VERSION,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY ,FRSPQTY ,FRSPPER,FRSPTIME,FWARECODE,FWARENAME,FSETS,FREASON,FSEATCODE,FSEATNAME,FUNIT,FFACTORYCODE,"
            + "FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,FPROCSS,FPARENTID,FHANDOVERPER,FISHANDOVER,FCODE ,FSEQCODE,FBATCH) SELECT #{fId},0 ,FPLANID ,FMACHINECODE,"
            + "FORDERNO,FMATCODE ,FMATNAME ,FOPTYPE,FRQSPER,FRQSTIME,FRQSQTY ,#{adjustQty} AS FRSPQTY,#{sCode} AS FRSPPER,SYSDATE AS FRSPTIME,"
            + "FWARECODE,FWARENAME ,FSETS,FREASON,FSEATCODE,FSEATNAME,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,"
            + " FPROCSS ,FID ,#{jobnum},'Y',FCODE,FSEQCODE,#{fBarCode} FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertDetailByParam(@Param("fId") String fId, @Param("adjustQty") double adjustQty, @Param("sCode") String sCode, @Param("jobnum") String jobnum, @Param("fBarCode") String fBarCode, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMATDETAIL (FID ,VERSION,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY ,FRSPQTY ,FRSPPER,FRSPTIME,FWARECODE,FWARENAME,FSETS,FREASON,FSEATCODE,FSEATNAME,FUNIT,FFACTORYCODE,"
            + "FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,FPROCSS,FPARENTID,FISHANDOVER,FCODE ,FSEQCODE,FBATCH) SELECT #{fId},0 ,FPLANID ,FMACHINECODE,"
            + "FORDERNO,FMATCODE ,FMATNAME ,FOPTYPE,FRQSPER,FRQSTIME,FRQSQTY ,#{originalQty} AS FRSPQTY,#{sCode} AS FRSPPER,SYSDATE AS FRSPTIME,"
            + "FWARECODE,FWARENAME ,FSETS,FREASON,FSEATCODE,FSEATNAME,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,"
            + " FPROCSS ,FID ,'Y',FCODE,FSEQCODE,#{fBarCode} FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertMgDetailByParam(@Param("fId") String fId, @Param("originalQty") double originalQty, @Param("sCode") String sCode, @Param("fBarCode") String fBarCode, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO TC_X5_LOGFILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,FORDERNO,FMATCODE,1,FWARECODE,FSEATCODE,#{adjustQty},sysdate,#{sCode},"
            + "#{fBarCode},FMACHINECODE,FREASON,FPROCSS ,#{fId} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertX5LogByParam(@Param("adjustQty") double adjustQty, @Param("sCode") String sCode, @Param("fBarCode") String fBarCode, @Param("fId") String fId, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO TC_X5_FILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,FORDERNO,FMATCODE,1,FWARECODE,FSEATCODE,#{adjustQty},sysdate,#{sCode},"
            + "#{fBarCode},FMACHINECODE,FREASON,FPROCSS ,#{fId} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertX5ByParam(@Param("adjustQty") double adjustQty, @Param("sCode") String sCode, @Param("fBarCode") String fBarCode, @Param("fId") String fId, @Param("fRecID") String fRecID);

    @Update("UPDATE MG_BATCHMAT SET FQTY=nvl(FQTY,0)-#{originalQty} WHERE FID=#{matID}")
    int updateBatchMatByParam(@Param("originalQty") double originalQty, @Param("matID") String matID);

    @Insert("insert into mg_batchmatrecord"
            + "  (fid, parantid, fvendercode, ftype, fbarcode, fcolor, fmatcode, fmatname, fbatchno, frecordtime, fqty, ffactorycode, fworkshopcode,FUNCTIONNAME,USERNAME,TYPE,STATUS)"
            + " select #{uuid},fid,fvendercode,ftype,fbarcode,fcolor,fmatcode, fmatname,fbatchno,sysdate,#{adjustQty},ffactorycode,fworkshopcode,'批次发料',#{sCode},#{fType},'5' from mg_batchmat "
            + " where fid = #{matID}")
    int insertBatchMatByParam(@Param("uuid") String uuid, @Param("adjustQty") double adjustQty, @Param("sCode") String sCode, @Param("fType") String fType, @Param("matID") String matID);
}
